USE [BS2]
GO
/****** Object:  View [dbo].[VW_Vouchers]    Script Date: 01/09/2015 23:21:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*---------------------------------------------------------------------------*/
ALTER VIEW [dbo].[VW_Vouchers]
AS
SELECT     dbo.VoucherDetails.VoucherID, dbo.Voucher.VoucherDate, dbo.Voucher.VoucherNo, ISNULL(dbo.Voucher.SVoucherNo, 0) AS SVoucherNo, 
                      dbo.Voucher.SessionID, dbo.SessionInfo.SessionTitle, dbo.SessionInfo.SessionFrom, dbo.SessionInfo.SessionTo, dbo.SessionInfo.CurrentSession, 
                      dbo.Voucher.Remarks AS MasterRemarks, dbo.Voucher.ReadOnly, dbo.VoucherDetails.VoucherDetailID, dbo.VoucherDetails.AccountNo, 
                      Accounts_4.AccountTitle, Accounts_4.AccountTitleUrdu, dbo.VoucherDetails.Remarks, CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Dr') 
                      THEN dbo.VoucherDetails.Amount ELSE - 1 * dbo.VoucherDetails.Amount END AS Amount, CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Dr') 
                      THEN dbo.VoucherDetails.Amount ELSE 0 END AS Debit, CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Cr') 
                      THEN dbo.VoucherDetails.Amount ELSE 0 END AS Credit, dbo.VoucherDetails.DrOrCr, Accounts_4.ParentAccount, dbo.Voucher.BankAccountID, 
                      Accounts_1.AccountTitle AS BankAccountTitle, Accounts_1.AccountTitleUrdu AS BankAccountTitleUrdu, Accounts_1.Remarks AS BankAccountAddress, 
                      Accounts_1.CellNo + '  ' + Accounts_1.Phone AS BankAccountContactNo, Accounts_1.LiscNo, Accounts_1.LiscDate, dbo.Voucher.SaleTax, 
                      dbo.Voucher.Expense, dbo.Voucher.Discount, dbo.Voucher.InvDiscPercent, dbo.Voucher.InvDiscount, dbo.Voucher.Cash, dbo.Voucher.AddLess, 
                      dbo.Voucher.PrvBalance, dbo.Voucher.LoginID, dbo.Employees.EmployeeName, dbo.Voucher.HostName, dbo.Voucher.EntryDateTime, 
                      dbo.VoucherDetails.Amount AS ActualAmount, dbo.Voucher.ModifyID, Employees_1.EmployeeName AS ModifyName, dbo.Voucher.ModifyHostName, 
                      dbo.Voucher.ModifyDateTime, dbo.Voucher.CustomerName, dbo.vw_Items.GroupID, dbo.vw_Items.GroupCode, dbo.vw_Items.GroupName, 
                      dbo.vw_Items.GroupNameUrdu, dbo.vw_Items.Type, dbo.VoucherDetails.ItemID, dbo.vw_Items.ItemCode, dbo.vw_Items.ItemName, 
                      dbo.vw_Items.ItemNameUrdu, dbo.vw_Items.InUnit, dbo.vw_Items.PRate, dbo.vw_Items.SRate, dbo.vw_Items.CompanyID, 
                      dbo.vw_Items.CompanyCode, dbo.vw_Items.CompanyName, dbo.vw_Items.CompanyNameUrdu, dbo.vw_Items.RackID, dbo.vw_Items.RackCode, 
                      dbo.vw_Items.RackName, dbo.vw_Items.RackNameUrdu, dbo.VoucherDetails.BatchNo, dbo.VoucherDetails.ExpiryDate, dbo.VoucherDetails.Packs, 
                      dbo.VoucherDetails.Lose, dbo.VoucherDetails.PSize, dbo.VoucherDetails.Qty, dbo.VoucherDetails.Bonus, dbo.VoucherDetails.PackRate, 
                      dbo.VoucherDetails.Rate, dbo.VoucherDetails.STaxPercent, dbo.VoucherDetails.STaxAmount, dbo.VoucherDetails.DiscPercent, 
                      dbo.VoucherDetails.DiscAmount, dbo.VoucherDetails.NetAmount, dbo.Voucher.BookedByID, Accounts_2.AccountTitle AS BookedByName, Accounts_2.AccountTitleUrdu AS BookedByNameUrdu, 
                      Accounts_2.CellNo AS BookedByCellNo, Accounts_2.Phone AS BookedByPhone, Accounts_2.Remarks AS BookedByAddress, 
                      Accounts_2.ComPercent AS BookedByComPercent, dbo.Voucher.DeliveredByID, Accounts_3.AccountTitle AS DeliveredByName, Accounts_3.AccountTitleUrdu AS DeliveredByNameUrdu, 
                      Accounts_3.CellNo AS DeliveredByCellNo, Accounts_3.Phone AS DeliveredByPhone, Accounts_3.Remarks AS DeliveredByAddress, 
                      Accounts_3.ComPercent AS DeliveredByComPercent, Accounts_1.AreaID, dbo.Area.AreaCode, dbo.Area.AreaName, dbo.Area.AreaNameUrdu, 
                      dbo.Area.RegionID, dbo.Region.RegionCode, dbo.Region.RegionName, dbo.Region.RegionNameUrdu, dbo.Voucher.Amount AS AmountM, 
                      dbo.Voucher.TotalAmount AS TotalAmountM, dbo.Voucher.NetAmount AS NetAmountM, dbo.Voucher.BillNo, dbo.Voucher.TransID, 
                      dbo.Accounts.AccountTitle AS TransName, dbo.Accounts.AccountTitleUrdu AS TransNameUrdu, dbo.Accounts.Remarks AS TransAddress, dbo.Accounts.CellNo AS TransCellNo, 
                      dbo.Accounts.Phone AS TransPhone
FROM         dbo.Accounts AS Accounts_1 LEFT OUTER JOIN
                      dbo.Region RIGHT OUTER JOIN
                      dbo.Area ON dbo.Region.RegionID = dbo.Area.RegionID ON Accounts_1.AreaID = dbo.Area.AreaID RIGHT OUTER JOIN
                      dbo.Accounts AS Accounts_3 RIGHT OUTER JOIN
                      dbo.Accounts RIGHT OUTER JOIN
                      dbo.Voucher ON dbo.Accounts.AccountNo = dbo.Voucher.TransID ON Accounts_3.AccountNo = dbo.Voucher.DeliveredByID LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_2 ON dbo.Voucher.BookedByID = Accounts_2.AccountNo ON 
                      Accounts_1.AccountNo = dbo.Voucher.BankAccountID LEFT OUTER JOIN
                      dbo.Employees AS Employees_1 ON dbo.Voucher.ModifyID = Employees_1.EmployeeID LEFT OUTER JOIN
                      dbo.Employees ON dbo.Voucher.LoginID = dbo.Employees.EmployeeID RIGHT OUTER JOIN
                      dbo.vw_Items RIGHT OUTER JOIN
                      dbo.VoucherDetails ON dbo.vw_Items.ItemID = dbo.VoucherDetails.ItemID LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_4 ON dbo.VoucherDetails.AccountNo = Accounts_4.AccountNo ON 
                      dbo.Voucher.VoucherID = dbo.VoucherDetails.VoucherID LEFT OUTER JOIN
                      dbo.SessionInfo ON dbo.Voucher.SessionID = dbo.SessionInfo.SessionID

-----------------------------------------------------------------------------